﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Text;
using System.Data;
using bzxxxt.model;
using bzxxxt.dal;
using bzxxxt.admin.service;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.SS.Formula.Eval;
namespace bzxxxt.bll
{
    public class ParadiseBLL : BaseBLL
    {
        admin.entity.UserEntity user = admin.service.UserService.GetUser(HttpContext.Current);   //获取cookie里的用户对象

        public string getUserList()
        {
            var list = from u in DB.tbUser
                       select new
                       {
                           id = u.id,
                           text = u.userName,
                           name = u.realName
                       };
            string json = JsonUtil.ToJson(list);
            return json;
        }

        public string GetAll()
        {
            if (user.roleNames.Contains("镇级管理员") || user.roleNames.Contains("骨灰堂管理员"))
            {
                var list = from p in DB.tbParadise
                           where user.paradiseIds.Contains(p.id)
                           select new
                           {
                               id = p.code,
                               text = p.name,
                               isGht = p.type=="骨灰堂"
                           };
                string json = JsonUtil.ToJson(list);
                return json;
            }
            else
            {
                var list = from p in DB.tbParadise                          
                           select new
                           {
                               id = p.code,
                               text = p.name,
                               isGht = p.type == "骨灰堂"
                           };
                string json = JsonUtil.ToJson(list);
                return json;
            }
        }
        public string GetListByPage(int pageSize, int pageIndex, string sort, string where = "1=1")
        {
            int totalCount = 0;
            if (user.roleNames.Contains("镇级管理员") || user.roleNames.Contains("骨灰堂管理员"))
            {
                string s = "";
                user.paradiseIds.ForEach(m => { s += m + ","; });
                s = s.Trim(',');
                where = string.Format("(id in ({0})) and ", s) + where;
            }
            
            SqlParameter paramTotalCount = new SqlParameter("@totalCount", totalCount);
            paramTotalCount.Direction = ParameterDirection.Output;
            var pp = DB.tbParadise.SqlQuery("exec sp_Pager @tableName,@columns,@order,@pageSize,@pageIndex,@where,@totalCount output",
                GetPagerParameters("tbParadise", "*", sort, where, pageSize, pageIndex, paramTotalCount)).ToList();
            totalCount = Convert.ToInt32(paramTotalCount.Value);

            var list = from p in pp
                       select new ParadiseModel
                           {
                               id = p.id,
                               code = p.code,
                               name = p.name,
                               fullName = p.fullName,
                               type = p.type,
                               pic = p.pic,
                               areacode = p.areacode,
                               paradiseNum = p.paradiseNum,
                               crdate = p.crdate,
                               areaName = p.tbArea == null ? "" : p.tbArea.name,
                               otherName = p.otherName,                            
                               graves = p.graves!=null ? p.graves.Value:0
                           };
            var data = list.ToList();
            data.ForEach(m =>
            {
                m.userIds = getUserIds(m.id);
                m.userNames = getUserNames(m.id);
            });
            var l = data.ToList();
            string json = JsonUtil.ToJson(new { total = totalCount, rows = l });
            return json;
        }

        public string GetCodeByAreaId(String code) {
            var area = DB.tbArea.FirstOrDefault(m => m.code == code);
            return area.scode;
        }

        private string getUserIds(int paradiseId)
        {
            var users = DB.tbUserParadise.Where(m => m.paradiseId == paradiseId);
            if (users.Count() == 0) return null;
            var ids = (from u in users select u.tbUser.id).ToList();
            string userIds = "";
            ids.ForEach(m => userIds += m + ",");
            return userIds.Trim(',');
        }
        private string getUserNames(int paradiseId)
        {
            var users = DB.tbUserParadise.Where(m => m.paradiseId == paradiseId);
            if (users.Count() == 0) return null;
            var ids = (from u in users select u.tbUser.userName).ToList();
            string userIds = "";
            ids.ForEach(m => userIds += m + ",");
            return userIds.Trim(',');
        }

        public string Add(tbParadise p)
        {
            var t = DB.tbParadise.Where(m => m.code == p.code);
            if (t.Count() > 0)
            {
                return JsonUtil.ToJson(new { msg = "编码已经存在!", success = false });
            }
            p.graves = 0;
            DB.tbParadise.Add(p);
            tbUserParadise tp = new tbUserParadise()
            {
                addTime = DateTime.Now,
                tbParadise = p,
                userId = user.id
            };
            DB.tbUserParadise.Add(tp);
            DB.SaveChanges();
            return JsonUtil.ToJson(new { msg = "添加成功!", success = true });
        }
        public string Edit(tbParadise p)
        {
            var o = DB.tbParadise.Where(m => m.id == p.id).FirstOrDefault();
            if (o != null)
            {
                o.name = p.name;
                o.fullName = p.fullName;
                o.code = p.code;
                o.type = p.type;
                o.areacode = p.areacode;
                o.pic = p.pic;
                o.paradiseNum = p.paradiseNum;
                o.otherName = p.otherName;
                //o.floors = p.floors;
                //o.eastSections = p.eastSections;
                //o.westSections = p.westSections;
                //o.lines = p.lines;
                //o.layers = p.layers;
                //o.columns = p.columns;
                //o.graves = p.graves;
                o.graves = o.tbGrave.Count();
                o.singles = o.tbGrave.Where(m => m.graveType == 1).Count();
                o.doubles = o.tbGrave.Where(m => m.graveType == 2).Count();
                o.multis = o.tbGrave.Where(m => m.graveType > 2).Count();
                o.esingles = o.tbGrave.Where(m => m.graveType == 1 && m.qty > 0).Count();
                o.edoubles = o.tbGrave.Where(m => m.graveType == 2 && m.qty > 0).Count();
                o.emultis = o.tbGrave.Where(m => m.graveType > 2 && m.qty > 0).Count();
            }
            DB.SaveChanges();
            return JsonUtil.ToJson(new { msg = "修改成功!", success = true });
        }

        


        
        public string Delete(string paradiseIds)
        {
            string[] ids = paradiseIds.Split(',');
            List<int> lstIds = new List<int>();
            foreach (var s in ids)
            {
                int id = int.Parse(s);
                var list = DB.tbParadise.SqlQuery("exec sp_delete_paradise_by_id @pid", new SqlParameter("@pid", id)).ToList();
            }
            //var list = DB.tbParadise.Where(m => lstIds.Contains(m.id)).ToList();
            //list.ForEach(m => DB.tbParadise.Remove(m));
            //DB.SaveChanges();            
            return JsonUtil.ToJson(new { msg = "删除成功!", success = true });
        }

        public string SetAdmin(int paradiseId, string userIds)
        {
            var olds = DB.tbUserParadise.Where(m => m.paradiseId == paradiseId).ToList();
            var oldIds = olds.Select(m => m.userId).ToList();
            List<int> newIds = new List<int>();
            if (!string.IsNullOrEmpty(userIds))
            {
                string[] ids = userIds.Split(',');
                foreach (var s in ids)
                {
                    int uid = int.Parse(s);
                    newIds.Add(uid);

                }
            }
            if (olds.Count > 0)
            {
                //计算共有的，保留
                var commIds = oldIds.Intersect(newIds).ToList();
                //从原来的中去除共有的，得到要删除的
                var removeIds = oldIds.Where(m => !commIds.Contains(m)).ToList();
                if (removeIds.Count > 0)
                {
                    olds.ForEach(m => { if (removeIds.Contains(m.userId)) DB.tbUserParadise.Remove(m); });
                }
                //从新的去除共有的，得到要添加的
                newIds.ForEach(m => { if (commIds.Contains(m)) newIds.Remove(m); });
            }
            newIds.ForEach(m =>
            {
                DB.tbUserParadise.Add(new tbUserParadise()
                {
                    paradiseId = paradiseId,
                    userId = m,
                    addTime = DateTime.Now
                });
            });


            DB.SaveChanges();
            return JsonUtil.ToJson(new { msg = "分配管理员成功!", success = true });
        }

        private bool needExclude(string num, List<int> excludes)
        {
            bool f = excludes.Any(m => num.IndexOf("" + m) >= 0);
            return f;
        }
        public string Init(ParadiseLayoutModel p, List<int> excludes)
        {
            //var graves = DB.tbGrave.Where(m => m.paradiseId == pid && m.hourseNum == hourseNum && m.floorNum == floorNum && m.sectionNum == sectionNum);
            //var gs = graves.ToList();
            //gs.ForEach(m => {
            //    var d = m.tbDead.ToList();
            //    d.ForEach(dd => m.tbDead.Remove(dd));
            //    DB.tbGrave.Remove(m);               
            //});
            var pp = DB.tbParadise.FirstOrDefault(m => m.id == p.id);
            string pcode = pp.code;
            List<int> list = new List<int>();
            string[] ls = p.lines.Split(',');
            int cnt = 0;
            char c = Convert.ToChar('A' + p.gtype - 1);
            StringBuilder sb = new StringBuilder();
            Dictionary<string, int> hGrave = new Dictionary<string, int>();
            foreach (string line in ls)
            {  //排
                int tlayers = p.layers;
                for (int lay = 1; lay <= tlayers; lay++)
                {
                    if (excludes.Any(m => (lay + "").IndexOf(m + "") >= 0)) { tlayers++; continue; }
                    int tcols = p.startIndex + p.columns;
                    for (int col = p.startIndex; col < tcols; col++)
                    {
                        
                        int num = col;
                        if (p.endNum != 0)
                        {
                            num = num * 10 + p.endNum;
                        }
                        if (excludes.Any(m => (num + "").IndexOf(m + "") >= 0)) { tcols++; continue; }
                        tbGrave g = new tbGrave()
                        {
                            paradiseId = p.id,
                            location = string.Format("{0:000}{1:0}0{2:00}{3:00}{4:00000}{5:0}", pcode, p.dong, p.section, int.Parse(line), num, c),
                            name = string.Format("{0:00}区{1:00}排{2:00000}号", p.section, int.Parse(line), num),
                            hourseNum = string.Format("{0:0}", p.dong),
                            floorNum = string.Format("{0:0}", p.floor),
                            sectionNum = string.Format("{0:00}", p.section),
                            lineNum = string.Format("{0:00}", int.Parse(line)),
                            layerNum = string.Format("{0:00}", lay),
                            columnNum = string.Format("{0:000}", num),
                            qty = 0,
                            graveType = p.gtype
                        };
                        if (pp.type == "骨灰堂")
                        {
                            g.location = string.Format("{0}{1}{2}{3}{4}{5}{6:000}{7}", pcode, g.hourseNum, g.floorNum, g.sectionNum, g.lineNum, g.layerNum, int.Parse(g.columnNum), c);
                            g.name = string.Format("{1}栋{2}楼{3}区{4}排{5}层{6:000}号", pcode, g.hourseNum, g.floorNum, g.sectionNum, g.lineNum, g.layerNum, int.Parse(g.columnNum));
                            g.columnNum = string.Format("{0:000}", int.Parse(g.columnNum));
                        }
                        else
                        {
                            int layer = num / 1000;
                            int cn = num % 1000;
                            g.floorNum = "0";
                            g.layerNum = string.Format("{0:00}", layer);
                            g.columnNum = string.Format("{0:000}", cn);

                        }
                        if (!hGrave.ContainsKey(g.location)) {
                            var gg = DB.tbGrave.FirstOrDefault(m => m.location.Substring(0, m.location.Length - 1) == g.location.Substring(0, g.location.Length - 1));
                            if (gg == null) DB.tbGrave.Add(g);
                            else
                            {
                                sb.AppendLine(g.location);
                            }
                            cnt++;
                        }
                        
                    }
                }
            }
            DB.SaveChanges();
            if (sb.ToString().Length > 0)
            {
                return JsonUtil.ToJson(new { msg = "初始化" + cnt + "个穴位成功![" + sb.ToString() + "]已经存在，未初始化!", success = true });
            }
            else
            {
                return JsonUtil.ToJson(new { msg = "初始化" + cnt + "个穴位成功!", success = true });
            }
        }
        
        /// <summary>
        /// 导入布局文件
        /// </summary>
        /// <param name="list"></param>
        /// <param name="excludes"></param>
        /// <returns></returns>
        public string Import(List<ParadiseLayoutModel> list,List<int> excludes)
        {
            StringBuilder sbMsg = new StringBuilder();
            foreach (ParadiseLayoutModel mm in list)
            {                
                try
                {
                    Init(mm,excludes);
                }
                catch (Exception ex) {
                    sbMsg.AppendFormat("第{5}行：{0}{1}{2}{3}{4}初始化失败！<br/>", mm.name, mm.dong, mm.floor, mm.section, mm.lines, mm.lineNum + 1);
                }
            }
            if (sbMsg.Length > 0)
            {
                string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
                FileStream fs = new FileStream(path, FileMode.OpenOrCreate);
                StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                sw.WriteLine("部分数据导入失败:<br/>");
                sw.WriteLine(sbMsg.ToString());
                sw.Close();
                throw new Exception("<a href='/admin/upload/log.html' target='_blank'>查看日志</a>");
            }
            return JsonUtil.ToJson(new { msg = "导入成功!", success = true });
        }

        /// <summary>
        /// 读取excel,导入布局文件
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet序号</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        public List<ParadiseLayoutModel> ImportExceltoDb(string strFileName, int SheetIndex, int HeaderRowIndex)
        {
            List<ParadiseLayoutModel> list = new List<ParadiseLayoutModel>();
            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create(file);
            }
            ISheet sheet = wb.GetSheetAt(SheetIndex);            
            DataTable table = new DataTable();
            IRow headerRow;
            int cellCount;
            StringBuilder sbMsg = new StringBuilder();
            try
            {
                //检查表格的标题是否符合模板格式

                headerRow = sheet.GetRow(HeaderRowIndex);
                cellCount = headerRow.LastCellNum;
                string[] headers = { "骨灰堂名", "栋号", "楼层", "厅区", "排号", "每排层数", "每层数量", "穴位类型", "开始编号", "末位数字" };
                							
                if (cellCount != headers.Length)
                {
                    throw new Exception("数据文件格式列数与模板列数不符！");
                }
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    string headtitle = headerRow.GetCell(i).ToString();
                    if (headers[i] != headtitle)
                    {
                        throw new Exception("数据文件第" + i + "列名称与数据模板不符！");
                    }
                }
                int rowCount = sheet.LastRowNum;

                StringBuilder sbLine = new StringBuilder();
                for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    ParadiseLayoutModel m = new ParadiseLayoutModel();
                    m.lineNum = i;
                    sbLine.Clear();
                    try
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            sbMsg.AppendFormat("第{0}行无数据!<br/>", i + 1);
                            continue;
                        }

                        //名称
                        m.name = getCellVaue(row.GetCell(0));
                        //处理名称
                        var c = DB.tbParadise.FirstOrDefault(p => p.name == m.name);
                        if (c == null)
                        {
                            sbLine.AppendFormat("第{0}行【{1}】名称无效! ", i + 1, m.name);
                        }
                        else
                        {
                            m.id = c.id;
                            m.code = c.code;
                        }
                        
                        string dong = getCellVaue(row.GetCell(1));
                        m.dong = int.Parse(dong);
                        
                        string floor = getCellVaue(row.GetCell(2));
                        m.floor = int.Parse(floor);

                        string section = getCellVaue(row.GetCell(3));
                        m.section = int.Parse(section);

                        string line = getCellVaue(row.GetCell(4));
                        m.lines = line;
                        if (string.IsNullOrEmpty(line))
                        {
                            sbLine.AppendFormat("第{0}行排号不能为空! ", i + 1);
                        }
                        else {
                            string[] ss = line.Split(',');
                            if (ss.Length<1) {
                                sbLine.AppendFormat("第{0}行排号有错! ", i + 1);
                            }                            
                        }

                        string layer = getCellVaue(row.GetCell(5));
                        m.layers = int.Parse(layer);

                        string col = getCellVaue(row.GetCell(6));
                        m.columns = int.Parse(col);

                        string type = getCellVaue(row.GetCell(7));
                        if (type == "单") m.gtype = 1;
                        else if (type == "双") m.gtype = 2;
                        else m.gtype = 3;
                        
                        string start = getCellVaue(row.GetCell(8));
                        m.startIndex = int.Parse(start);

                        string endNum = getCellVaue(row.GetCell(9));
                        m.endNum = int.Parse(endNum);

                    }
                    catch (Exception ex1)
                    {
                        sbLine.AppendFormat(" 第{0}行数据格式错误!" + ex1.Message, i + 1);
                    }

                    if (sbLine.Length > 0)
                    {
                        sbMsg.AppendLine(sbLine.ToString() + "<br/>");
                        sbLine.Clear();
                    }
                    else
                    {
                        list.Add(m);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            if (sbMsg.Length > 0)
            {
                string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
                FileStream fs = new FileStream(path, FileMode.Create);
                StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                sw.WriteLine("数据格式有误:<br/>");
                sw.WriteLine(sbMsg.ToString());
                sw.Close();
                throw new Exception("<a href='/admin/upload/log.html' target='_blank'>查看日志</a>");
            }
            sheet = null;
            return list;
        }

        private string getCellVaue(ICell cell)
        {
            string v = "";
            switch (cell.CellType)
            {
                case CellType.String:
                    v = cell.StringCellValue;

                    break;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        DateTime dt = DateTime.FromOADate(cell.NumericCellValue);
                        v = dt.ToString("yyyy-MM-dd");
                    }
                    else
                    {
                        v = Convert.ToString(cell.NumericCellValue);
                    }
                    break;
                case CellType.Boolean:
                    v = Convert.ToString(cell.BooleanCellValue);
                    break;
                case CellType.Error:
                    v = ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                default:
                    v = "";
                    break;
            }
            return v.Trim();
        }

    }
}